using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace ADOBase
{
    public abstract class DAOBase<T> : IDisposable
    {
        public static DateTime BeginDay(DateTime date)
        {
            return new DateTime(date.Year, date.Month, date.Day, 0, 0, 1);
        }
        public static DateTime EndDay(DateTime date)
        {
            return new DateTime(date.Year, date.Month, date.Day, 23, 59, 59);
        }
        #region Variables
        private IDbConnection _connection;
        private System.Data.IDbTransaction _transection;
        private bool _IsUseTransection = true;
        private bool _IsOnTransection = false;
        private bool _IsDebugMode = false;
        #endregion

        #region Properties
        public bool IsAutoTransection
        {
            get
            {
                return this._IsUseTransection;
            }
            set
            {
                this._IsUseTransection = value;
            }
        }
        public bool IsDebugMode
        {
            get
            {
                return this._IsDebugMode;
            }
            set
            {
                this._IsDebugMode = value;
            }
        }
        #endregion

        #region Constructor-Destructor
        public DAOBase()
        {
            this._IsDebugMode = DbConstants.IsOnDebugMode();
            //this.CreateConnection();
        }
        ~DAOBase()
        {
            //this.Dispose();
        }
        #endregion

        #region Create ADO.Net objects
        protected IDbCommand CreateCommand(string SQL)
        {
            IDbCommand cmd = CreateCommand();
            cmd.CommandText = SQL;
            return cmd;
        }
        protected IDbCommand CreateCommand()
        {
            IDbCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandTimeout = 10000000;
            return cmd;
        }
        protected IDbConnection CreateConnection()
        {
            if (this._connection == null)
            {
                this._connection = new System.Data.SqlClient.SqlConnection();
                this._connection.ConnectionString = DbConstants.ConnectionString;
            }
            if ((this._connection.State == ConnectionState.Closed) || (this._connection.State == ConnectionState.Broken))
            {
                try
                {
                    this._connection.Open();
                }
                catch (Exception ex)
                {
                    if (_IsDebugMode)
                    {
                        throw (ex);
                    }
                }
            }
            return this._connection;
        }
        protected IDbDataAdapter CreateAdapter()
        {
            System.Data.IDbDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter();
            return adp;
        }
        protected void CreateCommandParameter(IDbCommand cmd, string ParameterName, object Value)
        {
            IDataParameter par;
            par = cmd.CreateParameter();
            par.ParameterName = ParameterName;
            par.Value = Value;
            cmd.Parameters.Add(par);
        }
        #endregion

        #region Dispose Connections
        protected void CloseConnection()
        {
            if (this._connection == null) return;
            if (this._IsOnTransection)
            {
                this.CommitTransaction();
            }
            if (this._connection.State == ConnectionState.Open)
                this._connection.Close();
        }
        public void Dispose()
        {
            this.CloseConnection();
            if (this._connection == null) return;
            this._connection.Dispose();
        }
        #endregion

        #region Transections
        public void CreateTransaction()
        {
            if (this._IsOnTransection) return;
            if (this._connection == null) return;
            if (this._connection.State == ConnectionState.Closed) return;
            this._transection = this._connection.BeginTransaction();
            this._IsOnTransection = true;
        }
        public void RollBackTransaction()
        {
            if (this._connection == null) return;
            if (this._connection.State == ConnectionState.Closed) return;
            if (this._transection == null) return;
            this._transection.Rollback();
            this._IsOnTransection = false;
        }
        public void CommitTransaction()
        {
            if (this._connection == null) return;
            if (this._connection.State == ConnectionState.Closed) return;
            if (this._transection == null) return;
            this._transection.Commit();
            this._IsOnTransection = false;
        }
        #endregion

        #region Select functions
        protected abstract T MapObject(IDataReader reader);
        protected List<T> SelectList(string strSQL)
        {
            IDbCommand cmd = this.CreateCommand(strSQL);
            return this.SelectList(cmd);
        }
        protected List<T> SelectList(IDbCommand cmd)
        {
            this.CreateConnection();
            cmd.Connection = this._connection;
            List<T> list = new List<T>();
            IDataReader reader = null;
            if (this._IsUseTransection)
            {
                this.CreateTransaction();
                cmd.Transaction = this._transection;
            }
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(MapObject(reader));
                }
                try
                {
                    if (!reader.IsClosed) reader.Close();
                }
                catch
                {
                }
                if (this._IsUseTransection)
                {
                    this.CommitTransaction();
                }
            }
            catch (Exception ex)
            {
                try
                {

                    if (!reader.IsClosed) reader.Close();
                }
                catch
                {
                }
                if (this._IsUseTransection)
                {
                    this.RollBackTransaction();
                }
                if (_IsDebugMode)
                {
                    throw (ex);
                }
            }
            finally
            {
                if (this._IsUseTransection)
                {
                    this.CloseConnection();
                }
            }
            return list;
        }
        protected T Select(string strSQL)
        {
            IDbCommand cmd = this.CreateCommand(strSQL);
            return this.Select(cmd);
        }
        protected T Select(IDbCommand cmd)
        {
            this.CreateConnection();
            cmd.Connection = this._connection;
            if (this._IsUseTransection)
            {
                this.CreateTransaction();
                cmd.Transaction = this._transection;
            }
            bool successed = false;
            IDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
                reader.Read();
                successed = true;
            }
            catch (Exception ex)
            {
                successed = false;
                try
                {
                    if (!reader.IsClosed) reader.Close();
                }
                catch
                {
                }
                if (this._IsUseTransection)
                {
                    this.RollBackTransaction();
                }
                if (_IsDebugMode)
                {
                    throw (ex);
                }
            }
            T o = this.MapObject(reader);
            if (successed)
            {
                if (!reader.IsClosed) reader.Close();
                if (this._IsUseTransection)
                {
                    this.CommitTransaction();
                }
            }
            if (this._IsUseTransection)
            {
                this.CloseConnection();
            }
            return o;
        }
        protected DataSet SelectDS(string strSQL)
        {
            IDbCommand cmd = this.CreateCommand(strSQL);
            return this.SelectDS(cmd);
        }
        protected DataSet SelectDS(IDbCommand cmd)
        {
            DataSet dtSet = new DataSet();
            System.Data.IDbDataAdapter adp = this.CreateAdapter();
            this.CreateConnection();
            cmd.Connection = _connection;
            if (this._IsUseTransection)
            {
                this.CreateTransaction();
                cmd.Transaction = _transection;
            }
            try
            {
                adp.SelectCommand = cmd;
                adp.Fill(dtSet);
                if (this._IsUseTransection)
                {
                    this.CommitTransaction();
                }
            }
            catch
            {
                if (this._IsUseTransection)
                {
                    this.RollBackTransaction();
                }
            }
            finally
            {
                if (this._IsUseTransection)
                {
                    this.CloseConnection();
                }
            }
            return dtSet;
        }
        #endregion

        #region Execute command
        protected bool ExcuteNoneQuery(string SQL)
        {
            IDbCommand cmd = this.CreateCommand(SQL);
            return this.ExcuteNoneQuery(cmd);
        }
        protected object ExecuteScalar(string SQL, ref bool resultout)
        {
            IDbCommand cmd = this.CreateCommand(SQL);
            return this.ExecuteScalar(cmd, ref resultout);
        }
        protected object ExecuteScalar(IDbCommand cmd, ref bool resultout)
        {
            object result = null;
            this.CreateConnection();
            cmd.Connection = this._connection;
            if (_IsUseTransection)
            {
                this.CreateTransaction();
                cmd.Transaction = this._transection;
            }
            try
            {
                result = cmd.ExecuteScalar();
                if (_IsUseTransection)
                {
                    this.CommitTransaction();
                }
                resultout = true;
            }
            catch (Exception ex)
            {
                if (_IsUseTransection)
                {
                    this.RollBackTransaction();
                }
                if (_IsDebugMode)
                {
                    throw (ex);
                }
                resultout = false;
            }
            finally
            {
                cmd.Connection = null;
                this.CloseConnection();
            }
            return result;
        }
        protected bool ExcuteNoneQuery(IDbCommand cmd)
        {
            bool result = false;
            this.CreateConnection();
            cmd.Connection = _connection;
            if (this._IsUseTransection)
            {
                this.CreateTransaction();
                cmd.Transaction = this._transection;
            }
            try
            {
                cmd.ExecuteNonQuery();
                if (this._IsUseTransection)
                {
                    this.CommitTransaction();
                }
                result = true;
            }
            catch (Exception ex)
            {
                if (this._IsUseTransection)
                {
                    this.RollBackTransaction();
                }
                if (_IsDebugMode)
                {
                    throw (ex);
                }
            }
            finally
            {
                this.CloseConnection();
                cmd.Connection = null;
            }
            return result;
        }
        #endregion

        #region Command secured
        //Remove injection in string filter 
        protected string RemoveInjection(string strFilter)
        {
            string[] InjectionCodes = new string[] { "create table ", ".sysobjects ", "information_schema.", "@@version", "delete ", "drop table", "exec ", "+convert(", " convert(", "--" };
            foreach (string st in InjectionCodes)
            {
                strFilter = RemoveInjection(strFilter, st);
            }
            return strFilter;
        }
        private string RemoveInjection(string strFilter, string InjectionCode)
        {
            int i = 0;
            string st = strFilter.ToUpper();
            i = st.IndexOf(InjectionCode.ToUpper());
            while (i >= 0)
            {
                strFilter = strFilter.Remove(i, InjectionCode.Length);
                st = st.Remove(i, InjectionCode.Length);
                i = st.IndexOf(InjectionCode.ToUpper());
            }
            return strFilter;
        }
        #endregion
    }
}